﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using QuanLyCongViec.Database;

namespace QuanLyCongViec.Database
{
    class dbHopDong
    {
        SystemGlobal gb = new SystemGlobal();
        private SqlConnection conn;
        private SqlDataAdapter dadapt;
        private SqlCommand command;
        private string selectCommand;
        public dbHopDong()
        {
            string ConnectionString = @"Data Source=SERVER2K3\SQLSV2005;Initial Catalog=QLCongViec;User ID=sa;Password=123";
            conn = new SqlConnection(ConnectionString);
        }
        public DataTable HopDongGet(string strHopDongID,string strMaHopDong, string strMaLoaiHopDong)
        {
            if (strHopDongID != string.Empty)
            {
               selectCommand = @"select * from HopDong where (1 = 1) 
                                    clauseHopDongID
                                    clauseMaHopDong
                                    clauseMaLoaiHopDong";
            }
            else
            {
                selectCommand = @"select * from HopDong where (1 = 1) 
                                    clauseMaHopDong
                                    clauseMaLoaiHopDong";
            }
            string clauseHopDongID = "and HopDongID ='" + strHopDongID + "'"; 
            string clauseMaHopDong = "and MaHopDong like" + "'%" + strMaHopDong + "%'"; 
            string clauseMaLoaiHopDong = "and MaLoaiHopDong like" + "'%" + strMaLoaiHopDong + "%'";
            selectCommand = gb.Replace(selectCommand,"clauseHopDongID",clauseHopDongID,
                                                  "clauseMaHopDong",clauseMaHopDong,
                                                  "clauseMaLoaiHopDong",clauseMaLoaiHopDong);
                                                  
            dadapt = new SqlDataAdapter(selectCommand,conn);
            DataSet dsFinal = new DataSet();
            dadapt.Fill(dsFinal);
            return dsFinal.Tables[0];
        }
        public void HopDongCreate(string strNgayGiao, string strNgayHD, string strMaLoaiHD,string strMaHopDong)
        {
                conn.Open();
                string insertCommand = @"insert into HopDong(NgayGiaoHang,NgayHopDong,MaLoaiHopDong,MaHopDong) 
                                     values(strNgayGiao,strNgayHopDong,'strMaLoaiHopDong','strMaHopDong') ";
                insertCommand = gb.Replace(insertCommand, "strNgayGiao", strNgayGiao,
                                                      "strNgayHopDong", strNgayHD,
                                                      "strMaLoaiHopDong", strMaLoaiHD,
                                                      "strMaHopDong", strMaHopDong);
                command = new SqlCommand(insertCommand, conn);
                command.ExecuteNonQuery();
                conn.Close();
          
        }
        public void HopDongUpdate(string strHopDongID,string strNgayGiao, string strNgayHD, string strMaLoaiHD, string strMaHopDong)
        {
            conn.Open();
            string updateCommand = @"update HopDong set MaLoaiHopDong = 'strMaLoaiHopDong',
                                                        MaHopDong = 'strMaHopDong',
                                                        NgayGiaoHang = 'strNgayGiao',
                                                        NgayHopDong = 'strNgayHopDong'
                                        where HopDongID = strHopDongID";
            updateCommand = gb.Replace(updateCommand, "strMaLoaiHopDong", strMaLoaiHD,
                                                    "strMaHopDong", strMaHopDong,
                                                    "strNgayGiao", strNgayGiao,
                                                    "strNgayHopDong", strNgayHD,
                                                    "strHopDongID",strHopDongID);
            command = new SqlCommand(updateCommand, gb.conn);
            command.ExecuteNonQuery();
            conn.Close();
        }
        public void HopDongDelete(string strHopDongID)
        {
            conn.Open();
            string deleteCommand = @"delete from HopDong where HopDongID = strHopDongID";
            deleteCommand = gb.Replace(deleteCommand, "strHopDongID", strHopDongID);
            command = new SqlCommand(deleteCommand, conn);
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
}
